<?php

/**
 * 进销存系统调用sku基础资料系统的API类
 * User: kdw
 * Date: 14-8-26
 * Time: 下午3:14
 */
class Newerpclientapi
{
    private static function getClient($action)
    {
        require_once(Kohana::find_file('vendor','phprpc/phprpc_client'));
        $server_url = Kohana::$config->load('server_url');
        $client     = new PHPRPC_Client($server_url['server'].'/newerpserverapi/'.$action);
        return $client;
    }

    /*
    * 获取供应商信息
     * $fields 需要查询的字段
     * $condtions 查询的条件
    */
    public static function getSuppliers($fields)
    {
        $query = DB::select(array('supplier_id','id'),array('supplier_name','name'))->from('vmi_supplier_skus')->where('isavailable', '=',1);
        if(trim($fields)) {
            $query->where('supplier_name','like','%'. addslashes(trim($fields)).'%');
        }
        $su_ids = $query->group_by('supplier_id')->limit(15)->execute('newerp')->as_array();
        return $su_ids;
    }
    /**
     *  获取vmi系统供应商
     *  作者:kelvin
     * */
    public static function getSuppliersVmi($fields)
    {
        $su_ids = DB::select('supplier_id')->from('vmi_supplier_skus')->where('isavailable', '=',1)->group_by('supplier_id')->execute('newerp')->as_array('supplier_id');
        $suppliers = supplierskus::getSupplierNameById(array_keys($su_ids));
        $suList = array();
        $num = 0;
        foreach ($suppliers as $k =>$v){
            if(trim($fields) == '' && $num <15){
                $suList[] = array(
                    'id'=>$k,
                    'name'=>$v,
                );
            }elseif(strpos($v,$fields)!==false){
                $suList[] = array(
                    'id'=>$k,
                    'name'=>$v,
                );
            }
            $num +=1;
        }
        return $suList;
    }

    /*
     *根据根据供应商id获取到对应的联系人
     */
    public static function getSupplierContacts($supplierid)
    {
        $client   = self::getClient('putSupplierContacts');
        $contacts = $client->getinfo($supplierid);
        return $contacts;

        $suppliersContacts = array();
        $sql               = "SELECT id FROM skusystem_suppliers";
        if(isset($supplierid)){
            $sql .= " WHERE id=$supplierid AND isavailable=1";
        }


        $suppliers = DB::query(Database::SELECT,$sql)
            ->execute('newerp')
            ->as_array();
        foreach($suppliers as $supplier){
            $sql      = "SELECT c.id,phone,fax,contact,mobile,qq,wangwang
                        FROM skusystem_supplier_contacts sc,skusystem_contacts c
                        WHERE sc.contact_id = c.id AND sc.isavailable=1 AND sc.supplier_id = ".$supplier['id'];

            $contacts = DB::query(Database::SELECT,$sql)
                ->execute('newerp')
                ->as_array();

            foreach($contacts as $contact){
                $suppliersContacts[] = $contact;
            }
        }
        return $suppliersContacts;
    }

    /*
     *根据sku,供应商名称 匹配供应商是否存在
     */
    public static function matchSupplierBySku($sku,$suppliername)
    {
        /* $client = self::getClient('matchSupplierBySku');
         $result = $client->getinfo($sku,$suppliername);
         return $result;*/

        //sku和供应商匹配结果
        if(isset($suppliername) and isset($sku)){
            $sql   = "SELECT COUNT(0) AS count FROM skusystem_supplier_skus ss JOIN skusystem_suppliers s
                              ON ss.supplier_id=s.id WHERE s.isavailable=1 AND s.name LIKE '%".mysql_escape_string($suppliername)."%'
                              AND sku='".mysql_escape_string($sku)."'";
            $count = DB::query(Database::SELECT,$sql)
                ->execute('newerp')
                ->get('count');
            if($count){
                return true;
            }else{
                return false;
            }
        }
    }

    /*
     *根据sku查询供应商
     */
    public static function findSupplierBySku($sku)
    {
        /*$client    = self::getClient('findSupplierBySku');
        $suppliers = $client->getinfo($sku);
        return $suppliers;*/

        if(isset($sku)){
            $sql           = "SELECT ss.sku,ss.supplier_id,ss.price,ss.remark,ss.nuit,ss.productname,ss.size,
                                    ss.delivery,s.name FROM skusystem_supplier_skus ss JOIN skusystem_suppliers s ON ss.supplier_id=s.id
                                    WHERE ss.isavailable=1 AND ss.sku='".mysql_escape_string($sku)."'";
            $supplier_skus = DB::query(Database::SELECT,$sql)
                ->execute('newerp')
                ->as_array();
            return $supplier_skus;
        }
    }

    /*
     * 根据供应商名称模糊查询前15个供应商的名称
     */
    public static function getSupplierByName($name,$limit = 15)
    {
        /*$client = self::getClient('putSupplierByName');
        return $client->getinfo($name,$limit);*/

        return DB::query(Database::SELECT,'SELECT name FROM skusystem_suppliers WHERE name LIKE "%'.$name.'%" LIMIT 0,'.$limit)
            ->execute('newerp')
            ->as_array();

    }

    /*
     * 根据供应商id、sku模糊查询信息15条记录
     */
    public static function getSupplierSkuLike($supplier_id = null,$sku = NULL,$limit = NULL)
    {
        /*$client = self::getClient('putSupplierSkuLike');
        return $client->getinfo($supplier_id,$sku,$limit);*/
        $sql = "SELECT * FROM vmi_supplier_skus WHERE isavailable=1 ";
        if($supplier_id){
            $sql .= " AND supplier_id = $supplier_id";
        }
        if($sku){
            $sku = mysql_escape_string(trim($sku));
            $sql .= " AND sku LIKE '%$sku%'";
        }
        if(isset($limit)){
            $sql .= " LIMIT 0,".$limit;
        }
        return DB::query(Database::SELECT,$sql)
            ->execute('newerp')
            ->as_array();
    }

    /*
     * 根据供应商id,sku精确查询sku信息
     */
    public static function getSupplierSkuBy($supplier_id,$sku)
    {
        /*$client = self::getClient('putSupplierSkuBy');
        return $client->getinfo($supplier_id,$sku);*/

        $sql = "SELECT * FROM vmi_supplier_skus WHERE isavailable=1 AND supplier_id=".$supplier_id." AND sku='".$sku."'";
        return DB::query(Database::SELECT,$sql)
            ->execute('newerp')
            ->current();

    }

    /*
     * 联合查询供应商表和供应商产品表查询
     */
    public static function querySupplierSkuJoin($sku)
    {
        /*$client = self::getClient('querySupplierSkuJoin');
        return $client->getinfo($sku);*/

        $sql = "SELECT s.name AS vProviderName,ss.supplier_id AS iproviderID,ss.sku AS iproductID,
                    ss.price,ss.nuit,ss.productname AS productsname,ss.size,ss.delivery FROM skusystem_supplier_skus ss JOIN skusystem_suppliers s
                    ON ss.supplier_id=s.id WHERE ss.sku='$sku' AND ss.isavailable=1 AND s.isavailable=1";

        return DB::query(Database::SELECT,$sql)
            ->execute('newerp')
            ->as_array();

    }

    /*
     * 单独查询供应商sku表信息
     */
    public static function querySuppliersku($skus = NULL)
    {
        /*$client = self::getClient('querySuppliersku');
        return $client->getinfo($sku);*/

        $skuString = "";
        foreach($skus as $sku){
            $skuString .= $skuString?',"'.$sku.'"':'"'.$sku.'"';
        }
        $sql = "SELECT ss.supplier_id AS suppid,s.name AS vProviderName,ss.sku,ss.price,ss.delivery
                    FROM skusystem_supplier_skus ss JOIN skusystem_suppliers s
                    ON ss.supplier_id=s.id
                    WHERE ss.sku IN ($skuString) AND s.isavailable=1 AND ss.isavailable=1
                    ORDER BY ss.sku,ss.price";

        return DB::query(Database::SELECT,$sql)
            ->execute('newerp')
            ->as_array();

    }

    /*
     * 根据联系人id查询到联系人信息
     */
    public static function findContactById($contact_id)
    {
        $client = self::getClient('findContactById');
        return $client->getinfo($contact_id);
/*         return DB::query(Database::SELECT,'SELECT * FROM skusystem_contacts WHERE id='.$contact_id)
            ->execute('newerp')
            ->current(); */
    }

    /*
     * 根据指定的sku查询供应商sku信息
     */
    public static function querySupplierSkuBySkus($skus)
    {
        /*$client = self::getClient('querySupplierSkuBySkus');
        return $client->getinfo($skus);*/

        $skuString = "";
        foreach($skus as $sku){
            $skuString .= $skuString?',"'.$sku.'"':'"'.$sku.'"';
        }
        $sql = "SELECT ss.supplier_id AS suppid,s.name AS vProviderName,ss.sku,ss.price,ss.delivery
                    FROM skusystem_supplier_skus ss JOIN skusystem_suppliers s
                    ON ss.supplier_id=s.id
                    WHERE ss.sku IN ($skuString) AND s.isavailable=1 AND ss.isavailable=1
                    ORDER BY ss.sku,ss.price";

        return DB::query(Database::SELECT,$sql)
            ->execute('newerp')
            ->as_array();
    }

    /*
    * 根据sku查询供应商sku表中sku价格
    */
    public static function getSupplierskuPrice($sku)
    {
        /*$client = self::getClient('putSupplierskuPrice');
        return $client->getinfo($sku);*/

        $sql = "SELECT price FROM skusystem_supplier_skus WHERE sku='$sku' AND isavailable=1 ORDER BY price ASC";
        return DB::query(Database::SELECT,$sql)
            ->execute('newerp')
            ->as_array();

    }

    /*
     * 根据sku查询供应商sku表中的供应商id和sku名称
     */
    public static function getSupplierskuInfo($sku) {
        $client = self::getClient('putSupplierskuInfo');
        return $client->getinfo($sku);

        /*$sql = "SELECT s.id,s.name,ss.price,ss.delivery FROM skusystem_supplier_skus ss JOIN skusystem_suppliers s ON ss.supplier_id=s.id
                        WHERE sku='".$sku."' AND ss.isavailable=1 AND s.isavailable=1 ORDER BY ss.price";
        return DB::query(Database::SELECT,$sql)
            ->execute('newerp')
            ->as_array();*/

    }

    /*
     * 根据供应商id查询供应商
     */
    public static function getSupplierById($id)
    {
        /*$client = self::getClient('putSupplierById');
        return $client->getinfo($id);*/

        $id      = intval($id);
        $sql     = "SELECT name FROM skusystem_suppliers WHERE id = $id AND isavailable=1";
        $results = DB::query(Database::SELECT,$sql)
            ->execute('newerp')
            ->current();
        return $results;

    }

    /*
     * 根据供应商id批量查询供应商name
     */
    public static function getSuppliernameBatch($ids){
        if(!empty($ids)){
            /*$client = self::getClient('putSuppliernameBatch');
            return $client->getinfo($ids);*/
            $id=array_values($ids);
            return DB::select('id','name')->from('skusystem_suppliers')->where('id','in',$id)->execute('newerp')->as_array();
        }
    }

}